package func.common.excel;


import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;





/**
 * excel读取
 * @author chenyao
 * @date 2016年8月15日 上午10:35:38
 */
public class ExcelReader<T> {

	private final Class<T> clazz;
	private final Version version;
	
	private ExcelReader(Version version, Class<T> clazz) {
		super();
		this.version = version;
		this.clazz = clazz;
	}
    public static <T> ExcelReader<T> instanceOf2007(Class<T> clazz) {
        return new ExcelReader<T>(Version.EXCEL_2007, clazz);
    }

    public static <T> ExcelReader<T> instanceOf2003(Class<T> clazz) {
        return new ExcelReader<T>(Version.EXCEL_2003, clazz);
    }
	
	/**
	public static void main(String[] args) {
		ExcelReader<EmployeeUploadDto> reader = ExcelReader.instanceOf2007(EmployeeUploadDto.class);
		List<EmployeeUploadDto> list = reader.toList("D:/员工名单模板.xlsx",1,3);
		System.out.println(list);
	}
	*/
	public List<T> toList(String fileUrl , int titleLine, int dataStartLine) {
		List<T> list = Lists.newArrayList();
		Path p = Paths.get(fileUrl);
		try(InputStream in = Files.newInputStream(p)) {
			list = this.toList(in,titleLine,dataStartLine);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return list;
	}

	public List<T> toList(InputStream in) {
		List<T> list = Lists.newArrayList();
		try {
			Workbook workbook = this.version.getWorkbook(in);
			list = readFromWorkbook(workbook, 0, 1);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}		
		return list;
	}
	
	public List<T> toList(InputStream in, int titleLine, int dataStartLine) {
		List<T> list = Lists.newArrayList();
		try {
			Workbook workbook = this.version.getWorkbook(in);
			list = readFromWorkbook(workbook, titleLine, dataStartLine);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}		
		return list;
	}


	private List<T> readFromWorkbook(Workbook workbook, int titleLine, int dataStartLine) throws Exception {		
		List<T> list = new ArrayList<>();	
		// 得到第一个shell
		Sheet sheet = workbook.getSheetAt(0);
		// 得到Excel的行数
		int totalRows = sheet.getPhysicalNumberOfRows();
		//产品基础信息
		Row titleRow = sheet.getRow(titleLine);
		// 循环Excel行数,从第二行开始
		for (int r = dataStartLine; r < totalRows; r++) {
			Row contentRow = sheet.getRow(r);
			T t = this.create(titleRow, contentRow);			
			list.add(t);			
		}		
		return list;
	}
	
	private T create(Row titleRow, Row contentRow) throws Exception {
		T obj = this.clazz.newInstance();
		for(int i = 0; i < titleRow.getPhysicalNumberOfCells(); i++) {			
			String fieldName = getFieldName(titleRow.getCell(i));
			String fieldValue = parseCell(contentRow.getCell(i));			
			setFieldValue(obj, fieldName, fieldValue);
		}
		return obj;
	}
	private String getFieldName(Cell cell){
		String fieldName = "";
		String base = StringUtils.trim(parseCell(cell));
		if(StringUtils.isBlank(base)) {
			throw new RuntimeException("title cell is blank");
		}
		if(base.contains("[") && base.contains("]")) {
			int left = base.indexOf("[") + 1;
			int right = base.indexOf("]");
			fieldName = base.substring(left, right);
			
		} else {
			fieldName = base;
		}
		return fieldName;	
	}

	/**
	 * 把单元格数据转换成string类型
	 * @author chenyao
	 * @date 2016年12月31日 下午3:11:32
	 * @param cell
	 * @return
	 */
	private String parseCell(Cell cell) {  
        String result = new String(); 
        if(cell == null) {
        	return "";
        }
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
                SimpleDateFormat sdf = null;  
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
                    sdf = new SimpleDateFormat("HH:mm");  
                } else {// 日期  
                    sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
                }  
                Date date = cell.getDateCellValue();  
                result = sdf.format(date);  
            } else if (cell.getCellStyle().getDataFormat() == 58) {  
                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                result = sdf.format(date);  
            } else if (cell.getCellStyle().getDataFormat() == 57) {  
                // 处理自定义日期格式：y年m月(通过判断单元格的格式id解决，id的值是57)  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                result = sdf.format(date);  
            }else if (cell.getCellStyle().getDataFormat() == 56) {  
                // 处理自定义日期格式：m年(通过判断单元格的格式id解决，id的值是56)  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil  
                        .getJavaDate(value);  
                result = sdf.format(date);  
            } else {  
                double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();  
                DecimalFormat format = new DecimalFormat();  
                String temp = style.getDataFormatString();  
                // 单元格设置成常规  
                if (temp.equals("General")) {  
                    format.applyPattern("#");  
                }  
                result = format.format(value);  
            }  
            break;  
        case HSSFCell.CELL_TYPE_STRING:// String类型  
            result = StringUtils.trim(cell.getRichStringCellValue().toString());  
            break;
        case HSSFCell.CELL_TYPE_FORMULA:// 公式  
            result = StringUtils.trim(cell.getStringCellValue().toString());  
            break;
        case HSSFCell.CELL_TYPE_BLANK:  
            result = ""; 
            break;
        default:  
            result = "";  
            break;  
        }  
        return result;  
    }

	private void setFieldValue(Object obj, String fieldName, String fieldValue) throws Exception {
		Class<?> clazz = obj.getClass();
		Field idF = clazz.getDeclaredField(fieldName);
		PropertyDescriptor descriptor = new PropertyDescriptor(idF.getName(), clazz);
		Method method = descriptor.getWriteMethod();
		method.invoke(obj, convert(idF.getType(), fieldName, fieldValue));
	}

	private Object convert(Class<?> clazz, String fieldName, String val) {
		Object obj = null;
		try {
			if(StringUtils.isBlank(val)) {
				return obj;
			}
			if (clazz == String.class) {
				return val;
			}
			Constructor<?> constructor = clazz.getConstructor(String.class);
			obj = constructor.newInstance(val);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return obj;	
	}
	
	
	private enum Version {
		EXCEL_2003(".xls") {
			@Override
			public Workbook getWorkbook(InputStream in) throws IOException {
				return new HSSFWorkbook(in);
			}
		}, 
		EXCEL_2007(".xlsx") {
			@Override
			public Workbook getWorkbook(InputStream in) throws IOException {
				return new XSSFWorkbook(in);
			}
		};
		private final String postfix;
		private Version(String postfix) {
			this.postfix = postfix;
		}
		public String getPostfix() {
			return postfix;
		}
		public abstract Workbook getWorkbook(InputStream in) throws IOException;		
	}
	
	
}